﻿using System;
using System.Data;
using System.Data.SqlClient;

// Reference: http://dotnet-assembly.blogspot.com/2012/12/c-sql-helper-class.html
// Core library for accessing the database
namespace MvcCoffeeShop
{
    namespace DataAccess
    {
        public abstract class DataAccessBase
        {

     
            #region ExecuteDataTable

            public DataTable ExecuteDataTable(String strConn, String strProcedureName)
            {
                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);
                oCommand.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oAdapter.SelectCommand = oCommand;
                oConnection.Open();

                DataTable oDataTable = new DataTable();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        oAdapter.SelectCommand.Transaction = oTransaction;
                        oAdapter.Fill(oDataTable);
                        oTransaction.Commit();

                        return oDataTable;
                    }
                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        if (oConnection.State == ConnectionState.Open)
                        {
                            oConnection.Close();
                            oConnection.Dispose();
                            oAdapter.Dispose();
                        }
                    }
                }
            }

            public DataTable ExecuteDataTable(String strConn, String procedureName, SqlParameter[] parameters)
            {
                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
                oCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    oCommand.Parameters.AddRange(parameters);
                }

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oConnection.Open();

                DataTable oDataTable = new DataTable();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        oAdapter.SelectCommand.Transaction = oTransaction;
                        oAdapter.Fill(oDataTable);
                        oTransaction.Commit();
                        return oDataTable;
                    }
                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        if (oConnection.State == ConnectionState.Open)
                        {
                            oConnection.Close();
                            oConnection.Dispose();
                            oAdapter.Dispose();
                        }
                    }
                }
            }


            public DataTable ExecuteDataTable(String strConn, String procedureName, SqlParameter param)
            {
                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
                oCommand.CommandType = CommandType.StoredProcedure;

                if (param != null)
                {
                    oCommand.Parameters.Add(param);
                }

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oConnection.Open();

                DataTable oDataTable = new DataTable();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        oAdapter.SelectCommand.Transaction = oTransaction;
                        oAdapter.Fill(oDataTable);
                        oTransaction.Commit();
                        return oDataTable;
                    }
                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        if (oConnection.State == ConnectionState.Open)
                        {
                            oConnection.Close();
                            oConnection.Dispose();
                            oAdapter.Dispose();
                        }
                    }
                }
            }

            #endregion

            #region ExecuteDataSet
            public DataSet ExecuteDataSet(String strProcedureName, String strConn)
            {

                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);
                oCommand.CommandType = CommandType.StoredProcedure;

                DataSet oDataSet = new DataSet();

                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oAdapter.SelectCommand = oCommand;

                oConnection.Open();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {

                    try
                    {
                        oAdapter.SelectCommand.Transaction = oTransaction;
                        oAdapter.Fill(oDataSet);
                        oTransaction.Commit();
                        return oDataSet;
                    }
                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }

                    finally
                    {

                        if (oConnection.State == ConnectionState.Open)
                        {
                            oConnection.Close();
                            oConnection.Dispose();
                            oAdapter.Dispose();
                        }


                    }

                }

            }

            public DataSet ExecuteDataSet(String strProcedureName,
                                    String strConn,
                                    SqlParameter[] parameters)
            {
                SqlConnection oConnection = new SqlConnection(strConn);

                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);

                oCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    oCommand.Parameters.AddRange(parameters);
                }

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand; oConnection.Open();
                DataSet oDataSet = new DataSet();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        oAdapter.SelectCommand.Transaction = oTransaction;
                        oAdapter.Fill(oDataSet); oTransaction.Commit();
                        return oDataSet;
                    }
                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        if (oConnection.State == ConnectionState.Open)
                        {
                            oConnection.Close();
                            oConnection.Dispose();
                            oAdapter.Dispose();
                        }
                    }
                }
            }
            #endregion

            #region ExecuteScalar

            //Execute scalar with no parameters
            //Example Inserting a record and wanting a return id
            public static object ExecuteScalar(String strProcedureName, String strConn)
            {

                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);

                oCommand.CommandType = CommandType.StoredProcedure;

                object oReturnValue;

                oConnection.Open();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        oCommand.Transaction = oTransaction;
                        oReturnValue = oCommand.ExecuteScalar();
                        oTransaction.Commit();
                    }

                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }

                    finally
                    {

                        if (oConnection.State == ConnectionState.Open)

                            oConnection.Close();
                        oConnection.Dispose();
                        oCommand.Dispose();

                    }

                }

                return oReturnValue;

            }

            //Execute Scalar With Parameters
            //Example Inserting a record and wanting a return id
            public static object ExecuteScalar(String strProcedureName, SqlParameter[] parameters, String strConn)
            {
                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);
                oCommand.CommandType = CommandType.StoredProcedure;
                object oReturnValue; oConnection.Open();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        if (parameters != null)
                            oCommand.Parameters.AddRange(parameters);
                        oCommand.Transaction = oTransaction;
                        oReturnValue = oCommand.ExecuteScalar();
                        oTransaction.Commit();
                    }
                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        if (oConnection.State == ConnectionState.Open)
                            oConnection.Close();
                        oConnection.Dispose(); oCommand.Dispose();
                    }
                }
                return oReturnValue;
            }

            #endregion

            #region ExecuteNonQuery

            public static int ExecuteNonQuery(string strProcedureName, string strConn)
            {

                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);

                oCommand.CommandType = CommandType.StoredProcedure;

                int iReturnValue;

                oConnection.Open();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {
                    try
                    {
                        oCommand.Transaction = oTransaction;
                        iReturnValue = oCommand.ExecuteNonQuery();
                        oTransaction.Commit();
                    }

                    catch
                    {
                        oTransaction.Rollback();
                        throw;
                    }

                    finally
                    {

                        if (oConnection.State == ConnectionState.Open)
                        {
                            oConnection.Close();
                            oConnection.Dispose();
                            oCommand.Dispose();
                        }
                    }
                }

                return iReturnValue;

            }


            public static int ExecuteNonQuery(string strConn, string strProcedureName, SqlParameter[] parameters)
            {

                SqlConnection oConnection = new SqlConnection(strConn);
                SqlCommand oCommand = new SqlCommand(strProcedureName, oConnection);

                oCommand.CommandType = CommandType.StoredProcedure;

                int iReturnValue;

                oConnection.Open();

                using (SqlTransaction oTransaction = oConnection.BeginTransaction())
                {

                    try
                    {

                        if (parameters != null)
                            oCommand.Parameters.AddRange(parameters);
                        oCommand.Transaction = oTransaction;

                        iReturnValue = oCommand.ExecuteNonQuery();

                        oTransaction.Commit();

                    }

                    catch
                    {

                        oTransaction.Rollback();

                        throw;

                    }

                    finally
                    {

                        if (oConnection.State == ConnectionState.Open)

                            oConnection.Close();

                        oConnection.Dispose();

                        oCommand.Dispose();

                    }

                }

                return iReturnValue;

            }



            #endregion

        }
    }
}